﻿using System;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace VIT.Provider
{
    public class Account : VIT.DataHelper.DataAccess.SQLSupport
    {
        #region Variable
        public string UserName { get; set; }        
        public string Password { get; set; }
        public string FullName { get; set; }
        public string Roles { get; set; }
        string currentUser = HttpContext.Current.Session["userName"].ToString();
        string childUser = string.Empty;
        #endregion

        #region Constructor
        public Account(string userName = "",
                        string password = "",
                        string fullName = "",
                        string roles = "")
        {
            this.UserName = userName;
            this.Password = password;
            this.FullName = fullName;
            this.Roles = roles;
        }
        #endregion

        #region List
        public DataTable GetAll(string SortColumn = "", string ChildId = "Administrator", bool isTransaction = false)
        {
            string strQuery = "select UserName, FullName, CreateByUser from aUser";
            if (ChildId != "Administrator") 
                strQuery += " where UserName in ('" + ChildId.Replace(",", "','") + "') ";
            if(!string.IsNullOrEmpty(SortColumn))
                strQuery += "order by " + SortColumn;
            return ExecuteDataset(isTransaction, strQuery).Tables[0];
        }

        public bool Delete(string userName, bool isTransaction = false)
        {
            string strQuery = "Delete from aUser where UserName=@UserName";
            SqlParameter UserName = new SqlParameter("@UserName", userName);
            return ExecuteNonQuery(isTransaction, strQuery, UserName) > 0;
        }
        #endregion

        #region Edit
        public void GetSingle(bool isTransaction = false)
        {
            if (Exist(isTransaction))
            {
                string strQuery = "select UserName, FullName from aUser where UserName=@UserName";
                SqlParameter UserName = new SqlParameter("@UserName", this.UserName);
                DataTable dt = ExecuteDataset(isTransaction, strQuery, UserName).Tables[0];

                if (dt.Rows.Count > 0)
                {
                    this.UserName = dt.Rows[0]["UserName"].ToString();
                    this.FullName = dt.Rows[0]["FullName"].ToString();
                }
                else
                {
                    this.UserName = string.Empty;
                    this.FullName = string.Empty;
                }
            }
            else this.UserName = "";
        }

        public bool Exist(bool isTransaction = false)
        {
            string strQuery = "select UserName from aUser where UserName=@UserName";
            SqlParameter UserName = new SqlParameter("@UserName", this.UserName);
            return ExecuteScalar(isTransaction, strQuery, UserName) != null;
        }

        public bool Save(bool isTransaction = false)
        {
            if (Exist(isTransaction))
                return UpdateObj(isTransaction) > 0;
            return InsertObj(isTransaction) > 0;
        }

        public int InsertObj(bool isTransaction = false)
        {
            string strQuery = "insert into aUser(UserName, Password, FullName, CreateDate, ModifyDate, CreateByUser, ModifyByUser) values (@UserName, @Password, @FullName, @Now, @Now, @User, @User)";

            SqlParameter UserName = new SqlParameter("@UserName", this.UserName);
            SqlParameter Password = new SqlParameter("@Password", this.Password);
            SqlParameter FullName = new SqlParameter("@FullName", this.FullName);
            SqlParameter User = new SqlParameter("@User", currentUser);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(isTransaction, strQuery, UserName, Password, FullName, User, Now);
        }

        public int UpdateObj(bool isTransaction = false)
        {
            string strQuery = "update aUser set FullName=@FullName, ModifyDate=@Now, ModifyByUser=@User where UserName=@UserName";
            SqlParameter Usr = new SqlParameter("@UserName", this.UserName);            
            SqlParameter Fn = new SqlParameter("@FullName", this.FullName);
            SqlParameter User = new SqlParameter("@User", currentUser);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(isTransaction, strQuery, Usr, Fn, User, Now);
        }

        public bool ChangePassword(bool isTransaction = false)
        {
            string strQuery = "update aUser set Password=@Password where UserName=@UserName";
            SqlParameter UserName = new SqlParameter("@UserName", this.UserName);
            SqlParameter Password = new SqlParameter("@Password", this.Password);

            return ExecuteNonQuery(isTransaction, strQuery, UserName, Password) > 0;
        }
        #endregion

        #region Roles
        public DataTable GetAllRoles(string userName, string SortColumn = "Page", bool isTransaction = false)
        {
            string roles = GetRoles(userName);
            string strQuery = "select * from aFunction where Name in ('" + roles.Replace("|", "','") + "') order by " + SortColumn;
            return ExecuteDataset(isTransaction, strQuery).Tables[0];
        }

        public DataTable getRolesByPage(string userName, string page, string SortColumn = "Page", bool isTransaction = false)
        {
            string roles = GetRoles(userName);
            string strQuery = "select * from aFunction where Name in ('" + roles.Replace("|", "','") + "') and Page=@Page order by " + SortColumn;
            SqlParameter Page = new SqlParameter("@Page", page);
            return ExecuteDataset(isTransaction, strQuery, Page).Tables[0];
        }

        public bool SetRoles(bool isTransaction = false)
        {
            string strQuery = "update aUser set Roles=@Roles, ModifyDate=@Now, ModifyByUser=@User where UserName=@UserName";
            SqlParameter Usr = new SqlParameter("@UserName", this.UserName);
            SqlParameter roles = new SqlParameter("@Roles", this.Roles);
            SqlParameter User = new SqlParameter("@User", currentUser);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(isTransaction, strQuery, Usr, roles, User, Now) > 0;
        }

        public string GetRoles(string userName = "", bool isTransaction = false)
        {
            string strQuery = "select Roles from aUser where UserName=@UserName";
            SqlParameter Usr = new SqlParameter("@UserName", (userName == "") ? this.UserName : userName);
            return ExecuteScalar(isTransaction, strQuery, Usr).ToString();
        }
        #endregion

        #region Login
        public bool Login(bool isTransaction = false)
        {
            string strQuery = "select FullName, Roles from aUser where UserName=@UserName and Password=@Password";
            SqlParameter UserName = new SqlParameter("@UserName", this.UserName);
            SqlParameter Password = new SqlParameter("@Password", this.Password);
            DataSet ds = new DataSet();
            ds = ExecuteDataset(isTransaction, strQuery, UserName, Password);
            DataTable dt = ds.Tables[0];
            if (dt.Rows.Count>0)
            {
                this.FullName = dt.Rows[0]["FullName"].ToString();
                this.Roles = dt.Rows[0]["Roles"].ToString();
                return true;
            }
            return false;
        }
        #endregion

        #region setAdministratorRoles
        public void setAdministratorRoles(bool isTransaction = false)
        {
            string strQuery = "select * from aFunction";
            DataTable dt = ExecuteDataset(isTransaction, strQuery).Tables[0];
            string roles = "";
            foreach (DataRow r in dt.Rows)
            {
                roles += r["Name"].ToString() + "|";
            }

            strQuery = "update aUser set Roles = '" + roles + "' where UserName='Administrator'";
            ExecuteNonQuery(isTransaction, strQuery);
        }
        #endregion
    }
}
